
unit DBXDataSetTests;


interface

uses
  DB,
  SqlExpr,
  DBXConnection,
  DBXDataSetImpl,
  DBXDataSet,
  DBXQuery,
  DBXStoredProc,
  DBXTable,
  TestFrameWork;

  
type

  TDBXCommonTests = class(TTestCase)
  private
    FConnection: TDBXConnection;
    FDataSet: TCustomSQLDataSet;
    FDataSetExtension: IDBXDataSetExtension;
    FAssertFlag: Boolean;

  protected
    procedure SetUp; override;
    procedure TearDown; override;

    function CreateDataSet: TCustomSQLDataSet; virtual; abstract;
    procedure SetCustomerCommand; virtual; abstract;
    procedure SetCustomerEmptyCommand; virtual; abstract;
    procedure SetJobCommand; virtual; abstract;
    procedure SetJobEmptyCommand; virtual; abstract;

    procedure AfterDataSetConstruction(Sender: TObject);
    procedure BeforeDataSetDestruction(Sender: TObject);

    property DataSet: TCustomSQLDataSet read FDataSet;

  published
    // Test methods
    procedure TestCreate;
    procedure TestDestroy;
    procedure TestAfterConstruction;
    procedure TestBeforeDestruction;
    procedure TestPrepare;
    procedure TestUnprepare;
    procedure TestGetCurrentRecord;
    procedure TestGetFieldData;
    procedure TestGetBlobFieldData;
    procedure TestCreateBlobStream;
    procedure TestFetchAll;
    procedure TestApplyUpdates;
    procedure TestCommitUpdates;
    procedure TestCancelUpdates;
    procedure TestRevertRecord;
    procedure TestIsSequenced;
    procedure TestUpdateStatus;
    procedure TestBookmarkValid;
    procedure TestCompareBookmarks;
    procedure TestLocate;
    procedure TestLookup;
  end;


{ TDBXDataSetTests }

  TDBXDataSetTests = class(TDBXCommonTests)
  protected
    function CreateDataSet: TCustomSQLDataSet; override;
    procedure SetCustomerCommand; override;
    procedure SetCustomerEmptyCommand; override;
    procedure SetJobCommand; override;
    procedure SetJobEmptyCommand; override;
  end;


{ TDBXQueryTests }

  TDBXQueryTests = class(TDBXCommonTests)
  protected
    function CreateDataSet: TCustomSQLDataSet; override;
    procedure SetCustomerCommand; override;
    procedure SetCustomerEmptyCommand; override;
    procedure SetJobCommand; override;
    procedure SetJobEmptyCommand; override;
  published
    procedure TestPrepareStatement;
    procedure TestParseMacroSQL;
  end;


{ TDBXStoredProcTests }

  TDBXStoredProcTests = class(TDBXCommonTests)
  protected
    function CreateDataSet: TCustomSQLDataSet; override;
    procedure SetCustomerCommand; override;
    procedure SetCustomerEmptyCommand; override;
    procedure SetJobCommand; override;
    procedure SetJobEmptyCommand; override;
  end;


{ TDBXTableTests }

  TDBXTableTests = class(TDBXCommonTests)
  private
    procedure IgnoreRecord(DataSet: TDataSet; var Accept: Boolean);
  protected
    function CreateDataSet: TCustomSQLDataSet; override;
    procedure SetCustomerCommand; override;
    procedure SetCustomerEmptyCommand; override;
    procedure SetJobCommand; override;
    procedure SetJobEmptyCommand; override;
  end;


implementation

uses SysUtils, Variants, Classes, LXMacroSQL, DBXEvents, TestData;


{ TDBXCommonTests }

procedure TDBXCommonTests.SetUp;
begin
  inherited;
  FConnection := CreateTestConnection;
  FConnection.PoolOptions.Enabled := True;
  FDataSet := CreateDataSet;
  FDataSetExtension := FDataSet as IDBXDataSetExtension;
  FDataSet.SQLConnection := FConnection;
  SetCustomerCommand;
end;

procedure TDBXCommonTests.TearDown;
begin
  inherited;
  FreeAndNil(FConnection);
  FDataSetExtension := nil;
  FreeAndNil(FDataSet);
end;

procedure TDBXCommonTests.AfterDataSetConstruction(Sender: TObject);
begin
  FAssertFlag := True;
end;

procedure TDBXCommonTests.TestAfterConstruction;
begin
  with TDBXEvents.Create(nil) do
    try
      OnObjectCreate := AfterDataSetConstruction;
      CreateDataSet.Free;
      Assert(FAssertFlag);
    finally
      Free;
    end;
end;

procedure TDBXCommonTests.BeforeDataSetDestruction(Sender: TObject);
begin
  FAssertFlag := True;
end;

procedure TDBXCommonTests.TestBeforeDestruction;
begin
  with TDBXEvents.Create(nil) do
    try
      OnObjectCreate := AfterDataSetConstruction;
      CreateDataSet.Free;
      Assert(FAssertFlag);
    finally
      Free;
    end;
end;

procedure TDBXCommonTests.TestCreate;
begin
  Assert(FDataSet <> nil);
end;

procedure TDBXCommonTests.TestDestroy;
begin
  FDataSetExtension := nil;
  FreeAndNil(FDataSet);
end;

procedure TDBXCommonTests.TestPrepare;
begin
  FDataSetExtension.Prepare;
end;

procedure TDBXCommonTests.TestUnprepare;
begin
  FDataSetExtension.Unprepare;
  FDataSetExtension.Prepare;
  FDataSetExtension.Unprepare;
end;

procedure TDBXCommonTests.TestGetCurrentRecord;
begin
  Assert(not FDataSet.GetCurrentRecord(nil));
  FDataSet.Open;
  Assert(FDataSet.GetCurrentRecord(nil));
end;

procedure TDBXCommonTests.TestGetFieldData;
var
  Buffer: array of Byte;
begin
  FDataSet.Open;
  SetLength(Buffer, FDataSet.Fields[0].DataSize);
  FDataSet.Close;
  SetCustomerEmptyCommand;
  FDataSet.Open;
  Assert(not FDataSet.GetFieldData(FDataSet.Fields[0], Pointer(Buffer)));
end;

procedure TDBXCommonTests.TestGetBlobFieldData;
var
  Buffer: TBlobByteData;
begin
  FDataSet.Open;
  try
    FDataSet.GetBlobFieldData(1, Buffer);
    Fail('Cannot get blob data for a non blob field');
  except
    on EDatabaseError do;
  end;
  FDataSet.Close;
  SetJobCommand;
  FDataSet.Open;
  Assert(FDataSet.GetBlobFieldData(
    FDataSet.FieldByName('JOB_REQUIREMENT').FieldNo, Buffer) > 0);
  FDataSet.Close;
  SetJobEmptyCommand;
  FDataSet.Open;
  Assert(FDataSet.GetBlobFieldData(
    FDataSet.FieldByName('JOB_REQUIREMENT').FieldNo, Buffer) = 0);
end;

procedure TDBXCommonTests.TestCreateBlobStream;
var
  Stream: TStream;
begin
  FDataSet.Open;
  try
    FDataSet.CreateBlobStream(FDataSet.Fields[0], bmRead).Free;
    Fail('Cannot create blob stream for a non blob field');
  except
    on EDatabaseError do;
  end;
  FDataSet.Close;
  SetJobCommand;
  FDataSet.Open;
  Stream := FDataSet.CreateBlobStream(FDataSet.FieldByName('JOB_REQUIREMENT'), bmRead);
  try
    Assert((Stream <> nil) and (Stream.Size <> 0));
  finally
    FreeAndNil(Stream);
  end;
  FDataSet.Close;
  SetJobEmptyCommand;
  FDataSet.Open;
  Stream := FDataSet.CreateBlobStream(FDataSet.FieldByName('JOB_REQUIREMENT'), bmRead);
  try
    Assert((Stream <> nil) and (Stream.Size = 0));
  finally
    FreeAndNil(Stream);
  end;
end;

procedure TDBXCommonTests.TestFetchAll;
begin
  FDataSet.Open;
  FDataSetExtension.FetchAll;
end;

procedure TDBXCommonTests.TestApplyUpdates;
begin
  FConnection.ExecuteDirect('delete from customer where customer like ''DBX unit test record %''');

  FDataSetExtension.CachedUpdates := True;

  FDataSet.Open;
  FDataSet.Insert;
  FDataSet.FieldByName('CUST_NO').AsInteger := 0;
  FDataSet.FieldByName('CUSTOMER').AsString := 'DBX unit test record 1';
  FDataSet.Post;
  FDataSet.Insert;
  FDataSet.FieldByName('CUST_NO').AsInteger := 0;
  FDataSet.FieldByName('CUSTOMER').AsString := 'DBX unit test record 2';
  FDataSet.Post;
  FDataSetExtension.ApplyUpdates;

  FDataSet.Close;
  FDataSet.Open;
  Assert(FDataSet.Locate('CUSTOMER', 'DBX unit test record 1', []));
  FDataSet.Edit;
  FDataSet.FieldByName('CUSTOMER').AsString := 'DBX unit test record 3';
  FDataSet.Post;
  Assert(FDataSet.Locate('CUSTOMER', 'DBX unit test record 2', []));
  FDataSet.Delete;
  FConnection.ApplyUpdates([FDataSet]);

  FDataSet.Close;
  FDataSet.Open;
  Assert(not FDataSet.Locate('CUSTOMER', 'DBX unit test record 1', []));
  Assert(not FDataSet.Locate('CUSTOMER', 'DBX unit test record 2', []));
  Assert(FDataSet.Locate('CUSTOMER', 'DBX unit test record 3', []));
end;

procedure TDBXCommonTests.TestCommitUpdates;
begin
  FConnection.ExecuteDirect('delete from customer where customer like ''DBX unit test record %''');

  FDataSetExtension.CachedUpdates := True;
  FDataSetExtension.AutoCommitUpdates := False;

  FDataSet.Open;
  FDataSet.Insert;
  FDataSet.FieldByName('CUST_NO').AsInteger := 0;
  FDataSet.FieldByName('CUSTOMER').AsString := 'DBX unit test record 1';
  FDataSet.Post;
  FDataSet.Insert;
  FDataSet.FieldByName('CUST_NO').AsInteger := 0;
  FDataSet.FieldByName('CUSTOMER').AsString := 'DBX unit test record 2';
  FDataSet.Post;
  
  IProviderSupport(FDataSet).PSStartTransaction;
  try
    FDataSetExtension.ApplyUpdates;
  finally
    IProviderSupport(FDataSet).PSEndTransaction(False);
  end;

  FDataSetExtension.ApplyUpdates;
  FDataSetExtension.CommitUpdates;
  FDataSetExtension.ApplyUpdates;

  FDataSet.Close;
  FDataSet.Open;
  Assert(FDataSet.Locate('CUSTOMER', 'DBX unit test record 1', []));
  Assert(not FDataSetExtension.LocateNext('CUSTOMER', 'DBX unit test record 1', []));
  Assert(FDataSet.Locate('CUSTOMER', 'DBX unit test record 2', []));
  Assert(not FDataSetExtension.LocateNext('CUSTOMER', 'DBX unit test record 2', []));
end;

procedure TDBXCommonTests.TestCancelUpdates;
begin
  FConnection.ExecuteDirect('delete from customer where customer like ''DBX unit test record %''');

  FDataSetExtension.CachedUpdates := True;

  FDataSet.Open;
  FDataSet.Insert;
  FDataSet.FieldByName('CUST_NO').AsInteger := 0;
  FDataSet.FieldByName('CUSTOMER').AsString := 'DBX unit test record 1';
  FDataSet.Post;
  FDataSet.Insert;
  FDataSet.FieldByName('CUST_NO').AsInteger := 0;
  FDataSet.FieldByName('CUSTOMER').AsString := 'DBX unit test record 2';
  FDataSet.Post;

  FDataSet.Last;
  Assert(FDataSet.Locate('CUSTOMER', 'DBX unit test record 1', []));
  Assert(FDataSet.Locate('CUSTOMER', 'DBX unit test record 2', []));

  FDataSetExtension.CancelUpdates;

  FDataSet.Last;
  Assert(not FDataSet.Locate('CUSTOMER', 'DBX unit test record 1', []));
  Assert(not FDataSet.Locate('CUSTOMER', 'DBX unit test record 2', []));
end;

procedure TDBXCommonTests.TestRevertRecord;
begin
  FConnection.ExecuteDirect('delete from customer where customer like ''DBX unit test record %''');

  FDataSetExtension.CachedUpdates := True;

  FDataSet.Open;
  FDataSet.Insert;
  FDataSet.FieldByName('CUST_NO').AsInteger := 0;
  FDataSet.FieldByName('CUSTOMER').AsString := 'DBX unit test record 1';
  FDataSet.Post;

  FDataSet.Last;
  Assert(FDataSet.Locate('CUSTOMER', 'DBX unit test record 1', []));

  FDataSetExtension.RevertRecord;

  FDataSet.Last;
  Assert(not FDataSet.Locate('CUSTOMER', 'DBX unit test record 1', []));
end;

procedure TDBXCommonTests.TestIsSequenced;
begin
  FDataSet.Open;
  FDataSetExtension.FetchAll;
  Assert(FDataSet.IsSequenced);
end;

procedure TDBXCommonTests.TestUpdateStatus;
begin
  FConnection.ExecuteDirect('delete from customer where customer like ''DBX unit test record %''');

  FDataSetExtension.CachedUpdates := True;

  FDataSet.Open;
  FDataSet.Edit;
  FDataSet.FieldByName('CUST_NO').AsInteger := 0;
  FDataSet.FieldByName('CUSTOMER').AsString := 'DBX unit test record 1';
  FDataSet.Post;
  FDataSet.Insert;
  FDataSet.FieldByName('CUST_NO').AsInteger := 0;
  FDataSet.FieldByName('CUSTOMER').AsString := 'DBX unit test record 2';
  FDataSet.Post;

  FDataSet.Last;
  Assert(FDataSet.Locate('CUSTOMER', 'DBX unit test record 1', []));
  Assert(FDataSet.UpdateStatus = usModified);
  Assert(FDataSet.Locate('CUSTOMER', 'DBX unit test record 2', []));
  Assert(FDataSet.UpdateStatus = usInserted);

  FDataSetExtension.CancelUpdates;
end;

procedure TDBXCommonTests.TestBookmarkValid;
var
  P: Pointer;
begin
  Assert(not FDataSet.BookmarkValid(nil));
  FDataSet.Open;
  P := FDataSet.GetBookmark;
  try
    Assert(FDataSet.BookmarkValid(P));
    Assert(not FDataSet.BookmarkValid(PChar(P) + 1));
  finally
    FDataSet.FreeBookmark(P);
  end;
end;

procedure TDBXCommonTests.TestCompareBookmarks;
var
  P1, P2: Pointer;
begin
  Assert(FDataSet.CompareBookmarks(nil, nil) = 0);
  FDataSet.Open;
  P1 := FDataSet.GetBookmark;
  FDataSet.Next;
  P2 := FDataSet.GetBookmark;
  try
    Assert(FDataSet.CompareBookmarks(P1, P1) = 0);
    Assert(FDataSet.CompareBookmarks(P2, P1) <> 0);
  finally
    FDataSet.FreeBookmark(P1);
    FDataSet.FreeBookmark(P2);
  end;
end;

procedure TDBXCommonTests.TestLocate;
begin
  FConnection.ExecuteDirect('delete from customer where customer like ''DBX unit test record %''');

  FDataSetExtension.CachedUpdates := True;

  FDataSet.Open;

  FDataSet.Insert;
  FDataSet.FieldByName('CUST_NO').AsInteger := 1;
  FDataSet.FieldByName('CUSTOMER').AsString := 'DBX unit test record 1';
  FDataSet.Post;

  FDataSet.Last;

  FDataSet.Insert;
  FDataSet.FieldByName('CUST_NO').AsInteger := 2;
  FDataSet.FieldByName('CUSTOMER').AsString := 'DBX unit test record 2';
  FDataSet.Post;

  FDataSet.Next;

  Assert(FDataSet.Locate('CUST_NO', 2, []));
  Assert(FDataSet.Locate('CUST_NO', '2', []));
  Assert(FDataSet.Locate('CUST_NO', '002', []));
  Assert(FDataSet.Locate('CUST_NO', '  2', []));

  Assert(FDataSet.Locate('CUST_NO', 1, []));
  Assert(FDataSet.Locate('CUST_NO', '1', []));
  Assert(FDataSet.Locate('CUST_NO', '001', []));
  Assert(FDataSet.Locate('CUST_NO', '  1', []));

  Assert(FDataSet.Locate('CUSTOMER', 'DBX unit test record 2', []));
  Assert(FDataSet.Locate('CUSTOMER', 'DBX unit test record 1', []));

  Assert(FDataSet.Locate('CUST_NO;CUSTOMER',
    VarArrayOf([2, 'DBX unit test record 2']), []));
  Assert(FDataSet.Locate('CUST_NO;CUSTOMER',
    VarArrayOf([1, 'DBX unit test record 1']), []));

  Assert(not FDataSet.Locate('CUSTOMER', 'DBX unit TEST record 2', []));
  Assert(FDataSet.Locate('CUSTOMER', 'DBX unit TEST record 2', [loCaseInsensitive]));
  Assert(not FDataSet.Locate('CUSTOMER', 'DBX UNIT test record 1', []));
  Assert(FDataSet.Locate('CUSTOMER', 'DBX UNIT test record 1', [loCaseInsensitive]));

  Assert(not FDataSet.Locate('CONTACT_FIRST;CUSTOMER',
    VarArrayOf([Null, 'DBX UNIT test record 1']), [loCaseInsensitive]));

  Assert(FDataSet.Locate('CUSTOMER', 'DBX unit TEST record', [loCaseInsensitive, loPartialKey]));
  Assert(FDataSetExtension.LocateNext('CUSTOMER', 'DBX unit TEST record', [loCaseInsensitive, loPartialKey]));
  Assert(not FDataSetExtension.LocateNext('CUSTOMER', 'DBX unit TEST record', [loCaseInsensitive, loPartialKey]));

  FDataSetExtension.CancelUpdates;
end;

procedure TDBXCommonTests.TestLookup;
begin
  FConnection.ExecuteDirect('delete from customer where customer like ''DBX unit test record %''');

  FDataSetExtension.CachedUpdates := True;

  FDataSet.Open;

  FDataSet.Insert;
  FDataSet.FieldByName('CUST_NO').AsInteger := 1;
  FDataSet.FieldByName('CUSTOMER').AsString := 'DBX unit test record 1';
  FDataSet.Post;

  FDataSet.Last;

  FDataSet.Insert;
  FDataSet.FieldByName('CUST_NO').AsInteger := 2;
  FDataSet.FieldByName('CUSTOMER').AsString := 'DBX unit test record 2';
  FDataSet.Post;

  FDataSet.Next;

  Assert(FDataSet.Lookup('CUST_NO', 1, 'CUSTOMER') = 'DBX unit test record 1');
  Assert(FDataSet.Lookup('CUST_NO', 2, 'CUSTOMER') = 'DBX unit test record 2');

  Assert(FDataSet.Lookup('CUSTOMER', 'DBX unit test record 1', 'CUST_NO') = 1);
  Assert(FDataSet.Lookup('CUSTOMER', 'DBX unit test record 2', 'CUST_NO') = 2);

  FDataSetExtension.CancelUpdates;
end;


{ TDBXDataSetTests }

function TDBXDataSetTests.CreateDataSet: TCustomSQLDataSet;
begin
  Result := TDBXDataSet.Create(nil);
end;

procedure TDBXDataSetTests.SetCustomerCommand;
begin
  TDBXDataSet(DataSet).CommandText := 'select * from customer';
end;

procedure TDBXDataSetTests.SetCustomerEmptyCommand;
begin
  TDBXDataSet(DataSet).CommandText := 'select * from customer where 1=0';
end;

procedure TDBXDataSetTests.SetJobCommand;
begin
  TDBXDataSet(DataSet).CommandText := 'select * from job';
end;

procedure TDBXDataSetTests.SetJobEmptyCommand;
begin
  TDBXDataSet(DataSet).CommandText := 'select * from job where 1=0';
end;


{ TDBXQueryTests }

function TDBXQueryTests.CreateDataSet: TCustomSQLDataSet;
begin
  Result := TDBXQuery.Create(nil);
end;

procedure TDBXQueryTests.SetCustomerCommand;
begin
  TDBXQuery(DataSet).SQL.Text := 'select * from customer';
end;

procedure TDBXQueryTests.SetCustomerEmptyCommand;
begin
  TDBXQuery(DataSet).SQL.Text := 'select * from customer where 1=0';
end;

procedure TDBXQueryTests.SetJobCommand;
begin
  TDBXQuery(DataSet).SQL.Text := 'select * from job';
end;

procedure TDBXQueryTests.SetJobEmptyCommand;
begin
  TDBXQuery(DataSet).SQL.Text := 'select * from job where 1=0';
end;

procedure TDBXQueryTests.TestPrepareStatement;
begin
  DataSet.SQLConnection.Open;
  TDBXQuery(DataSet).PrepareStatement;
  TDBXQuery(DataSet).SQL.Text := '#ifdef';
  try
    TDBXQuery(DataSet).PrepareStatement;
    Fail('Here must be ELXMacroSQLError');
  except
    on ELXMacroSQLError do;
  end;
end;

procedure TDBXQueryTests.TestParseMacroSQL;
begin
  TDBXQuery(DataSet).ParseMacroSQL;
  TDBXQuery(DataSet).SQL.Text := '#ifdef';
  try
    TDBXQuery(DataSet).ParseMacroSQL;
    Fail('Here must be ELXMacroSQLError');
  except
    on ELXMacroSQLError do;
  end;
end;


{ TDBXStoredProcTests }

function TDBXStoredProcTests.CreateDataSet: TCustomSQLDataSet;
begin
  Result := TDBXStoredProc.Create(nil);
end;

procedure TDBXStoredProcTests.SetCustomerCommand;
begin
  TDBXStoredProc(DataSet).StoredProcName := 'all_customers';
end;

procedure TDBXStoredProcTests.SetCustomerEmptyCommand;
begin
  TDBXStoredProc(DataSet).StoredProcName := 'all_customers';
end;

procedure TDBXStoredProcTests.SetJobCommand;
begin
  TDBXStoredProc(DataSet).StoredProcName := 'all_customers';
end;

procedure TDBXStoredProcTests.SetJobEmptyCommand;
begin
  TDBXStoredProc(DataSet).StoredProcName := 'all_customers';
end;


{ TDBXTableTests }

procedure TDBXTableTests.IgnoreRecord(DataSet: TDataSet; var Accept: Boolean);
begin
  Accept := False;
end;

function TDBXTableTests.CreateDataSet: TCustomSQLDataSet;
begin
  Result := TDBXTable.Create(nil);
  Result.OnFilterRecord := IgnoreRecord;
end;

procedure TDBXTableTests.SetCustomerCommand;
begin
  TDBXTable(DataSet).TableName := 'customer';
  TDBXTable(DataSet).Filtered := False;
end;

procedure TDBXTableTests.SetCustomerEmptyCommand;
begin
  TDBXTable(DataSet).TableName := 'customer';
  TDBXTable(DataSet).Filtered := True;
end;

procedure TDBXTableTests.SetJobCommand;
begin
  TDBXTable(DataSet).TableName := 'job';
  TDBXTable(DataSet).Filtered := False;
end;

procedure TDBXTableTests.SetJobEmptyCommand;
begin
  TDBXTable(DataSet).TableName := 'job';
  TDBXTable(DataSet).Filtered := True;
end;


initialization

  TestFramework.RegisterTest('DBXDataSetTests Suite',
    TDBXDataSetTests.Suite);
  TestFramework.RegisterTest('DBXQueryTests Suite',
    TDBXQueryTests.Suite);
  //TestFramework.RegisterTest('DBXStoredProcTests Suite',
  //  TDBXStoredProcTests.Suite);
  TestFramework.RegisterTest('DBXTableTests Suite',
    TDBXTableTests.Suite);

end.
